/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;


import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.LocationCostHistory;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;
import java.util.ArrayList;
import java.sql.Timestamp;
import java.util.Iterator;
/**
 *
 * @author AnhTuan
 */
public class LocationCostHistoryDAOImpl extends DatabaseConnection implements LocationCostHistoryDAO {

    public Iterator<LocationCostHistory> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From LocationCostHistory");
            while (rs.next()) {
                LocationCostHistory row = new LocationCostHistory();
                row.setLocationID(rs.getInt("LocationID"));
                row.setStartDate(rs.getTimestamp("StartDate"));
                row.setStandardCost(rs.getInt("StandardCost"));
                
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    // get Cost History
    public Iterator<LocationCostHistory> getHistoryData(int locationID, int numRow, Timestamp fromDate, Timestamp toDate)
    {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_getCostHistory(?,?,?,?)}");
            cst.setInt(1, locationID);
            cst.setInt(2, numRow);
            cst.setTimestamp(3, fromDate);
            cst.setTimestamp(4, toDate);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                LocationCostHistory row = new LocationCostHistory();
                row.setStandardCost(rs.getInt("StandardCost"));
                row.setStartDate(rs.getTimestamp("StartDate"));
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public LocationCostHistory getRow(int locationID )
    {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select TOP 1 * From LocationCostHistory Where LocationID=? " +
                    " ORDER BY StartDate DESC");
            pst.setInt(1, locationID);
            ResultSet rs = pst.executeQuery();
            LocationCostHistory row = new LocationCostHistory();
            if (rs.next()) {
                row.setLocationID(rs.getInt("LocationID"));
                row.setStartDate(rs.getTimestamp("StartDate"));
                row.setStandardCost(rs.getInt("StandardCost"));
            }
            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean checkDuplicate(int locationID, Timestamp startDate)
    {
        try {
            closeConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From LocationCostHistory Where LocationID=? AND StartDate=?");
            pst.setInt(1, locationID);
            pst.setTimestamp(2, startDate);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(LocationCostHistory object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into LocationCostHistory(LocationID,StartDate,StandardCost) Values(?,?,?)");
            pst.setInt(1, object.getLocationID());     
            pst.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
            pst.setInt(3, object.getStandardCost());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(LocationCostHistory object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update LocationCostHistory Set StandardCost=? Where LocationID=? AND StartDate=?");
            pst.setInt(1, object.getStandardCost());
            pst.setInt(2, object.getLocationID());
            pst.setTimestamp(3, object.getStartDate());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int locationID, Timestamp startDate) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete LocationCostHistory Where LocationID=? AND StartDate=?");
            pst.setInt(1, locationID);
            pst.setTimestamp(2, startDate);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
    
}
